<?php

include('includes/session.inc');

$Title = _('All Stock Status By Location/Category');

include('includes/header.inc');

if (isset($_GET['StockID'])) {
	$StockID = trim(mb_strtoupper($_GET['StockID']));
} elseif (isset($_POST['StockID'])) {
	$StockID = trim(mb_strtoupper($_POST['StockID']));
}

echo '<p class="page_title_text noPrint" ><img src="' . $RootPath . '/css/' . $Theme . '/images/magnifier.png" title="' . _('Search') . '" alt="" />' . ' ' . $Title . '</p>';

echo '<form onSubmit="return VerifyForm(this);" action="' . htmlspecialchars($_SERVER['PHP_SELF'], ENT_QUOTES, 'UTF-8') . '" method="post" class="noPrint">';
echo '<input type="hidden" name="FormID" value="' . $_SESSION['FormID'] . '" />';

echo '<table class="selection">
	 <tr>
		 <td>' . _('From Stock Location') . ':</td><td><select minlength="0" name="StockLocation">';

if ($_SESSION['RestrictLocations'] == 0) {
	$sql = "SELECT locationname,
					loccode
				FROM locations";
	echo '<option selected="selected" value="All">' . _('All Locations') . '</option>';
	if (!isset($_POST['StockLocation'])) {
		$_POST['StockLocation'] = 'All';
	}
} else {
	$sql = "SELECT locationname,
					loccode
				FROM locations
				INNER JOIN www_users
					ON locations.loccode=www_users.defaultlocation
				WHERE www_users.userid='" . $_SESSION['UserID'] . "'";
	if (!isset($_POST['StockLocation'])) {
		$_POST['StockLocation'] = $_SESSION['UserStockLocation'];
	}
}

$resultStkLocs = DB_query($sql, $db);
while ($myrow = DB_fetch_array($resultStkLocs)) {
	if (isset($_POST['StockLocation']) and $_POST['StockLocation'] != 'All') {
		if ($myrow['loccode'] == $_POST['StockLocation']) {
			echo '<option selected="selected" value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
		} else {
			echo '<option value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
		}
	} else {
		echo '<option value="' . $myrow['loccode'] . '">' . $myrow['locationname'] . '</option>';
	}
}
echo '</select></td>
		</tr>';

$SQL = "SELECT categoryid,
				categorydescription
		FROM stockcategory
		ORDER BY categorydescription";
$result1 = DB_query($SQL, $db);
if (DB_num_rows($result1) == 0) {
	echo '</table><p>';
	prnMsg(_('There are no stock categories currently defined please use the link below to set them up'), 'warn');
	echo '<br /><a href="' . $RootPath . '/StockCategories.php">' . _('Define Stock Categories') . '</a>';
	include('includes/footer.inc');
	exit;
}

echo '<tr>
		<td>' . _('In Stock Category') . ':</td>
		<td><select required="required" minlength="1" name="StockCat">';
if (!isset($_POST['StockCat'])) {
	$_POST['StockCat'] = 'All';
}
if ($_POST['StockCat'] == 'All') {
	echo '<option selected="selected" value="All">' . _('All') . '</option>';
} else {
	echo '<option value="All">' . _('All') . '</option>';
}
while ($myrow1 = DB_fetch_array($result1)) {
	if ($myrow1['categoryid'] == $_POST['StockCat']) {
		echo '<option selected="selected" value="' . $myrow1['categoryid'] . '">' . $myrow1['categorydescription'] . '</option>';
	} else {
		echo '<option value="' . $myrow1['categoryid'] . '">' . $myrow1['categorydescription'] . '</option>';
	}
}

echo '</select></td></tr>';

echo '<tr>
		<td>' . _('Shown Only Items Where') . ':</td>
		<td><select required="required" minlength="1" name="BelowReorderQuantity">';
if (!isset($_POST['BelowReorderQuantity'])) {
	$_POST['BelowReorderQuantity'] = 'All';
}
if ($_POST['BelowReorderQuantity'] == 'All') {
	echo '<option selected="selected" value="All">' . _('All') . '</option>
		  <option value="Below">' . _('Only items below re-order quantity') . '</option>
		  <option value="NotZero">' . _('Only items where stock is available') . '</option>
		  <option value="OnOrder">' . _('Only items currently on order') . '</option>';
} else if ($_POST['BelowReorderQuantity'] == 'Below') {
	echo '<option value="All">' . _('All') . '</option>
		  <option selected="selected" value="Below">' . _('Only items below re-order quantity') . '</option>
		  <option value="NotZero">' . _('Only items where stock is available') . '</option>
		  <option value="OnOrder">' . _('Only items currently on order') . '</option>';
} else if ($_POST['BelowReorderQuantity'] == 'OnOrder') {
	echo '<option value="All">' . _('All') . '</option>
		  <option value="Below">' . _('Only items below re-order quantity') . '</option>
		  <option value="NotZero">' . _('Only items where stock is available') . '</option>
		  <option selected="selected" value="OnOrder">' . _('Only items currently on order') . '</option>';
} else {
	echo '<option value="All">' . _('All') . '</option>
		  <option value="Below">' . _('Only items below re-order quantity') . '</option>
		  <option selected="selected" value="NotZero">' . _('Only items where stock is available') . '</option>
		  <option value="OnOrder">' . _('Only items currently on order') . '</option>';
}

echo '</select></td></tr>
	 </table>';

echo '<div class="centre">
		  <input type="submit" name="ShowStatus" value="' . _('Show Stock Status') . '" />
	 </div>';

if (isset($_POST['ShowStatus'])) {

	if ($_POST['StockCat'] == 'All') {
		$_POST['StockCat'] = '%';
	}
	if ($_POST['StockLocation'] == 'All') {
		$_POST['StockLocation'] = '%';
	}
	$sql = "SELECT locstock.stockid,
					stockmaster.description,
					locstock.loccode,
					locstock.bin,
					locations.locationname,
					locstock.quantity,
					locstock.reorderlevel,
					stockmaster.decimalplaces,
					stockmaster.serialised,
					stockmaster.controlled
				FROM locstock
				INNER JOIN stockmaster
					ON locstock.stockid=stockmaster.stockid
				INNER JOIN locations
					ON locstock.loccode=locations.loccode
				WHERE locstock.loccode " . LIKE . "'" . $_POST['StockLocation'] . "'
					AND (stockmaster.mbflag='B' OR stockmaster.mbflag='M')
					AND stockmaster.categoryid " . LIKE . "'" . $_POST['StockCat'] . "'
				ORDER BY locstock.stockid";

	$ErrMsg = _('The stock held at each location cannot be retrieved because');
	$DbgMsg = _('The SQL that failed was');
	$LocStockResult = DB_query($sql, $db, $ErrMsg, $DbgMsg);

	echo '<table cellpadding="5" cellspacing="4" class="selection">
		 	<tr>
				<th>' . _('Location') . '</th>
				<th>' . _('StockID') . '</th>
				<th>' . _('Description') . '</th>
				<th>' . _('Quantity On Hand') . '</th>
				<th>' . _('Re-Order Level') . '</th>
				<th>' . _('Demand') . '</th>
				<th>' . _('Available') . '</th>
				<th>' . _('On Order') . '</th>
				<th>' . _('Need To Order(ROL)') . '</th>
			</tr>';
	$k = 0; //row colour counter

	while ($myrow = DB_fetch_array($LocStockResult)) {

		$StockID = $myrow['stockid'];

		$sql = "SELECT SUM(salesorderdetails.quantity-salesorderdetails.qtyinvoiced) AS dem
					FROM salesorderdetails INNER JOIN salesorders
					ON salesorders.orderno = salesorderdetails.orderno
					WHERE salesorders.fromstkloc='" . $myrow['loccode'] . "'
					AND salesorderdetails.completed=0
					AND salesorderdetails.stkcode='" . $StockID . "'
					AND salesorders.quotation=0";

		$ErrMsg = _('The demand for this product from') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
		$DemandResult = DB_query($sql, $db, $ErrMsg);

		if (DB_num_rows($DemandResult) == 1) {
			$DemandRow = DB_fetch_row($DemandResult);
			$DemandQty = $DemandRow[0];
		} else {
			$DemandQty = 0;
		}

		//Also need to add in the demand as a component of an assembly items if this items has any assembly parents.
		$sql = "SELECT SUM((salesorderdetails.quantity-salesorderdetails.qtyinvoiced)*bom.quantity) AS dem
				FROM salesorderdetails INNER JOIN salesorders
					 ON salesorders.orderno = salesorderdetails.orderno
				INNER JOIN bom
					  ON salesorderdetails.stkcode=bom.parent
				INNER JOIN stockmaster
					  ON stockmaster.stockid=bom.parent
				WHERE salesorders.fromstkloc='" . $myrow['loccode'] . "'
				AND salesorderdetails.quantity-salesorderdetails.qtyinvoiced > 0
				AND bom.component='" . $StockID . "'
				AND stockmaster.mbflag='A'
				AND salesorders.quotation=0";

		$ErrMsg = _('The demand for this product from') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
		$DemandResult = DB_query($sql, $db, $ErrMsg);

		if (DB_num_rows($DemandResult) == 1) {
			$DemandRow = DB_fetch_row($DemandResult);
			$DemandQty += $DemandRow[0];
		}
		$sql = "SELECT SUM((woitems.qtyreqd-woitems.qtyrecd)*bom.quantity) AS dem
				FROM workorders INNER JOIN woitems
					 ON woitems.wo = workorders.wo
				INNER JOIN bom
					  ON woitems.stockid =  bom.parent
				WHERE workorders.closed=0
				AND   bom.component = '" . $StockID . "'
				AND   workorders.loccode='" . $myrow['loccode'] . "'";
		$DemandResult = DB_query($sql, $db, $ErrMsg);

		if (DB_num_rows($DemandResult) == 1) {
			$DemandRow = DB_fetch_row($DemandResult);
			$DemandQty += $DemandRow[0];
		}


		$sql = "SELECT SUM(purchorderdetails.quantityord - purchorderdetails.quantityrecd) AS qoo
				FROM purchorderdetails
				INNER JOIN purchorders
					ON purchorderdetails.orderno=purchorders.orderno
				WHERE purchorders.intostocklocation='" . $myrow['loccode'] . "'
					AND purchorderdetails.itemcode='" . $StockID . "'
					AND (purchorders.status = 'Authorised' OR purchorders.status='Printed')";

		$ErrMsg = _('The quantity on order for this product to be received into') . ' ' . $myrow['loccode'] . ' ' . _('cannot be retrieved because');
		$QOOResult = DB_query($sql, $db, $ErrMsg);

		if (DB_num_rows($QOOResult) == 1) {
			$QOORow = DB_fetch_row($QOOResult);
			$QOO = $QOORow[0];
		} else {
			$QOO = 0;
		}

		if (($_POST['BelowReorderQuantity'] == 'Below' and ($myrow['quantity'] - $myrow['reorderlevel'] - $DemandQty) < 0) or $_POST['BelowReorderQuantity'] == 'All' or $_POST['BelowReorderQuantity'] == 'NotZero' or ($_POST['BelowReorderQuantity'] == 'OnOrder' and $QOO != 0)) {

			if (($myrow['quantity'] - $DemandQty) < $myrow['reorderlevel']) {
				$ReorderQty = $myrow['reorderlevel'] - ($myrow['quantity'] - $DemandQty);
			} else {
				$ReorderQty = 0;
			}

			if (($_POST['BelowReorderQuantity'] == 'NotZero') and (($myrow['quantity'] - $DemandQty) > 0)) {

				if ($k == 1) {
					echo '<tr class="OddTableRows">';
					$k = 0;
				} else {
					echo '<tr class="EvenTableRows">';
					$k = 1;
				}
				printf('<td>%s</td>
						<td><a target="_blank" href="' . $RootPath . '/StockStatus.php?StockID=%s">%s</a></td>
						<td>%s</td>
						<td class="number">%s</td>
						<td class="number">%s</td>
						<td class="number">%s</td>
						<td class="number"><a target="_blank" href="' . $RootPath . '/SelectProduct.php?StockID=%s">%s</a></td>
						<td class="number">%s</td>
						<td class="number">%s</td>',
						mb_strtoupper($myrow['locationname']),
						mb_strtoupper($myrow['stockid']),
						mb_strtoupper($myrow['stockid']),
						$myrow['description'],
						locale_number_format($myrow['quantity'], $myrow['decimalplaces']),
						locale_number_format($myrow['reorderlevel'], $myrow['decimalplaces']),
						locale_number_format($DemandQty, $myrow['decimalplaces']),
						mb_strtoupper($myrow['stockid']),
						locale_number_format($myrow['quantity'] - $DemandQty, $myrow['decimalplaces']),
						locale_number_format($QOO, $myrow['decimalplaces']),
						locale_number_format($ReorderQty, $myrow['decimalplaces'])
					);

				if ($myrow['serialised'] == 1) {
					/*The line is a serialised item*/

					echo '<td><a target="_blank" href="' . $RootPath . '/StockSerialItems.php?Serialised=Yes&Location=' . $myrow['loccode'] . '&StockID=' . $StockID . '">' . _('Serial Numbers') . '</a></td></tr>';
				} elseif ($myrow['controlled'] == 1) {
					echo '<td><a target="_blank" href="' . $RootPath . '/StockSerialItems.php?Location=' . $myrow['loccode'] . '&StockID=' . $StockID . '">' . _('Batches') . '</a></td></tr>';
				}
			} else if ($_POST['BelowReorderQuantity'] != 'NotZero') {
				if ($k == 1) {
					echo '<tr class="OddTableRows">';
					$k = 0;
				} else {
					echo '<tr class="EvenTableRows">';
					$k = 1;
				}
				printf('<td>%s</td>
						<td><a target="_blank" href="' . $RootPath . '/StockStatus.php?StockID=%s">%s</a></td>
						<td>%s</td>
						<td class="number">%s</td>
						<td class="number">%s</td>
						<td class="number">%s</td>
						<td class="number"><a target="_blank" href="' . $RootPath . '/SelectProduct.php?StockID=%s">%s</a></td>
						<td class="number">%s</td>
						<td class="number">%s</td>',
						mb_strtoupper($myrow['locationname']),
						mb_strtoupper($myrow['stockid']),
						mb_strtoupper($myrow['stockid']),
						$myrow['description'],
						locale_number_format($myrow['quantity'], $myrow['decimalplaces']),
						locale_number_format($myrow['reorderlevel'], $myrow['decimalplaces']),
						locale_number_format($DemandQty, $myrow['decimalplaces']),
						mb_strtoupper($myrow['stockid']), locale_number_format($myrow['quantity'] - $DemandQty, $myrow['decimalplaces']),
						locale_number_format($QOO, $myrow['decimalplaces']),
						locale_number_format($ReorderQty, $myrow['decimalplaces'])
					);
				if ($myrow['serialised'] == 1) {
					/*The line is a serialised item*/

					echo '<td><a target="_blank" href="' . $RootPath . '/StockSerialItems.php?Serialised=Yes&Location=' . $myrow['loccode'] . '&StockID=' . $StockID . '">' . _('Serial Numbers') . '</a></td></tr>';
				} elseif ($myrow['controlled'] == 1) {
					echo '<td><a target="_blank" href="' . $RootPath . '/StockSerialItems.php?Location=' . $myrow['loccode'] . '&StockID=' . $StockID . '">' . _('Batches') . '</a></td></tr>';
				}
			} //end of page full new headings if
		} //end of if BelowOrderQuantity or all items
	}
	//end of while loop

	echo '</table>';
}
/* Show status button hit */
echo '</form>';
include('includes/footer.inc');

?>